CREATE TABLE places
(
	place_id NUMBER NOT NULL PRIMARY KEY,
	place_name VARCHAR2(50) NOT NULL
);

CREATE TABLE events
(
	event_id NUMBER NOT NULL PRIMARY KEY,
	event_name VARCHAR2(50) NOT NULL,
	place_id NUMBER NOT NULL
);

CREATE TABLE devices
(
	device_id NUMBER NOT NULL PRIMARY KEY,
	type VARCHAR2(25) NOT NULL,
	bandwidth NUMBER NOT NULL,
	ports_number NUMBER NOT NULL
);

CREATE TABLE history
(
	history_id NUMBER NOT NULL PRIMARY KEY,
	event_date DATE NOT NULL,
	device_id NUMBER NOT NULL,
	quantity NUMBER NOT NULL,
	event_id NUMBER NOT NULL
);

CREATE SEQUENCE place_id_seq;
CREATE SEQUENCE event_id_seq;
CREATE SEQUENCE device_id_seq;
CREATE SEQUENCE history_id_seq;

ALTER TABLE events ADD CONSTRAINT events_ref FOREIGN KEY (place_id) REFERENCES places(place_id);
ALTER TABLE history ADD CONSTRAINT history_devices_ref FOREIGN KEY (device_id) REFERENCES devices(device_id);
ALTER TABLE history ADD CONSTRAINT history_events_ref FOREIGN KEY (event_id) REFERENCES events(event_id);

INSERT INTO places VALUES (place_id_seq.NEXTVAL, 'Main office');
INSERT INTO places VALUES (place_id_seq.NEXTVAL, 'Area A');
INSERT INTO places VALUES (place_id_seq.NEXTVAL, 'Area B');

INSERT INTO events VALUES (event_id_seq.NEXTVAL, 'Acceptance on balance', 1);
INSERT INTO events VALUES (event_id_seq.NEXTVAL, 'Installation', 2);
INSERT INTO events VALUES (event_id_seq.NEXTVAL, 'Installation', 3);

INSERT INTO devices VALUES (device_id_seq.NEXTVAL, 'Hub', 10, 8);
INSERT INTO devices VALUES (device_id_seq.NEXTVAL, 'Switch', 10, 16);
INSERT INTO devices VALUES (device_id_seq.NEXTVAL, 'Switch', 100, 32);
INSERT INTO devices VALUES (device_id_seq.NEXTVAL, 'Switch', 100, 16);

INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('25.07.2001', 'dd.mm.yyyy'), 1, 20, 1);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('26.07.2001', 'dd.mm.yyyy'), 2, 4, 1);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('14.08.2001', 'dd.mm.yyyy'), 1, 12, 1);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('21.08.2001', 'dd.mm.yyyy'), 1, 8, 2);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('23.08.2001', 'dd.mm.yyyy'), 1, 3, 3);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('01.09.2001', 'dd.mm.yyyy'), 2, 1, 2);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('03.09.2001', 'dd.mm.yyyy'), 3, 5, 1);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('07.09.2001', 'dd.mm.yyyy'), 3, 2, 1);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('14.09.2001', 'dd.mm.yyyy'), 1, 4, 2);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('21.09.2001', 'dd.mm.yyyy'), 4, 12, 1);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('25.09.2001', 'dd.mm.yyyy'), 1, 8, 2);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('02.10.2001', 'dd.mm.yyyy'), 2, 1, 3);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('08.10.2001', 'dd.mm.yyyy'), 1, 4, 3);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('10.10.2001', 'dd.mm.yyyy'), 4, 3, 3);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('12.10.2001', 'dd.mm.yyyy'), 3, 1, 1);
INSERT INTO history VALUES (history_id_seq.NEXTVAL, TO_DATE('14.10.2001', 'dd.mm.yyyy'), 4, 4, 2);

COMMIT;